Entity-Relationship Model

Entity

is an object that exists and is distinguishable from other objects. Represented by a rectangle in the E-R diagram.

Entity Set

A set of entities that has the same type and share the same properties.

Attribute

Represented by an ellipse in E-R diagram.

Relationship

An association among entities.

Relationship

A set of relationship of the same type. Represented by diamonds.

Mapping Cardinalities

Concerns the number of entities to which another entities can be associated via a relationship set

Participation Constraints

Concerns whether all entities in the entity set have to participate in the relationship set.

To indicate mapping cardinality, draw a directed arrow \(\to\) signifying one between the relationship set and the entity; draw an undirected line \(-\) signifying many between the relationship set and the entity.

To indicate participation constraints, use double line to indicate total participation (all entity must do); use single line to indicate partial participation.

Keys are used to determine an entity bby attributes.

Super Key

A set of one or more attributes whose values uniquely determine each entity.

Candidate Key

A minimal¹ super key.

Primary Key

One of the candidate keys is selected to be the primary key. Underline the attribute to indicate the primary key

Weak Entity Set

refers to entity sets whose attributes are not sufficient to form a primary key to uniquely identify each entity. Denoted by double triangles in diagrams.

Therefore, weak entity set depends on other entity sets to identify its own entities. The entity sets that weak entity sets depends on are called identifying entity sets.

To connect weak entity sets to their identifying entity sets, we should involve identifying relationship set, denoted by double diamonds. The relationship between weak and identifying entity sets must be total and many-to-one or one-to-one.

For a weak entity set, the primary key is formed by the primary key of the identifying entity set plus the discriminator of the weak entity set. The discriminator is denoted by dashed line.

Roles

The labels are called roles, as they specify how these entity sets interact via relationship sets.

Specialization

Specialization is to designate subgroupings within an entity set that are distinctive from other entities in this set. Denoted by a “IS A” triangle in the E-R Diagram.

For specialization, lower-level entities automatically inherits all attributes and relationship set participation of higher-level entity set to which it’s linked. Moreover, they can have their own attributes.

• Completeness Constraints:

  Total specialization

  An entity in a higher-level entity set must belong to at least one of the lower-level entity sets within a specialization. Denoted by double line in ISA diagram.

  Partial specialization

  An entity in a higher-level entity set may not belong to any lower-level entity sets within the specialization. Denoted by single line in ISA diagram.

• Disjointness Constraints:

  Disjoint specialization

  Entities may only belong to no more than one lower-level entity sets. Denoted by a keyword “disjoint” in the diagram.

  Overlapping specialization

  Entities may belong to more than one lower-level entity sets. Does not require any keyword notations.

Single vs. Composite Attributes

A composite attribute can be further decomposed into several single attributes, called component attributes.

Single-valued vs. Multi-valued Attributes

Multi-valued attributes can have multiple values on an attribute (e.g., multiple phone numbers on the “phone” attribute). Multi-valued attribute is denoted by double ellipse.

Derived Attributes

The value in derived attributes can be derived from other attributes, e.g., “age” can be computed from “date_ofbirth”. Denoted by dashed ellipse.

Some common design principles:

• Use relationship sets to describe an action that occurs between entities.